using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace IFXplus.Northwind
{
    public static class CustomerDataAccess
    {
        private static readonly string ConnectionStringProviderName = "NorthwindExpress";
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringProviderName].ConnectionString;

        public static List<Customer> FindAll()
        {
            SqlConnection cn = null;
            try
            {
                string sql = "SELECT * FROM Customers";
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader dr = cmd.ExecuteReader();
                List<Customer> cl = DataReader2CustomerList(dr);
                dr.Close();
                dr.Dispose();
                cmd.Dispose();
                return cl;
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in CustomerDataAccess.FindAll", ex);
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }

        public static Customer Find(string customerid)
        {
            SqlConnection cn = null;
            Customer customer = null;
            try
            {
                string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@CustomerId", customerid);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                    customer = DataReader2Customer(dr);
                dr.Close();
                dr.Dispose();
                cmd.Dispose();
                return customer;
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in CustomerDataAccess.Find", ex);
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }

        public static void Create(Customer customer)
        {
            SqlConnection cn = null;
            try
            {
                string sql =
                    "INSERT INTO Customers ( " +
                        "CustomerId, " +
                        "CompanyName, " +
                        "ContactName, " +
                        "ContactTitle, " +
                        "Address, " +
                        "City, " +
                        "Region, " +
                        "PostalCode, " +
                        "Country, " +
                        "Phone, " +
                        "Fax) " +
                    "VALUES ( " +
                        "@CustomerId, " +
                        "@CompanyName, " +
                        "@ContactName, " +
                        "@ContactTitle, " +
                        "@Address, " +
                        "@City, " +
                        "@Region, " +
                        "@PostalCode, " +
                        "@Country, " +
                        "@Phone, " +
                        "@Fax)";
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@CustomerId", customer.CustomerId);
                cmd.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
                cmd.Parameters.AddWithValue("@ContactName", customer.ContactName);
                cmd.Parameters.AddWithValue("@ContactTitle", customer.ContactTitle);
                cmd.Parameters.AddWithValue("@Address", customer.Address);
                cmd.Parameters.AddWithValue("@City", customer.City);
                cmd.Parameters.AddWithValue("@Region", customer.Region);
                cmd.Parameters.AddWithValue("@PostalCode", customer.PostalCode);
                cmd.Parameters.AddWithValue("@Country", customer.Country);
                cmd.Parameters.AddWithValue("@Phone", customer.Phone);
                cmd.Parameters.AddWithValue("@Fax", customer.Fax);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in CustomerDataAccess.Create", ex);
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }

        public static void Update(Customer customer)
        {
            SqlConnection cn = null;
            try
            {
                string sql =
                    "UPDATE Customers SET " +
                        "CompanyName = @CompanyName, " +
                        "ContactName = @ContactName, " +
                        "ContactTitle = @ContactTitle, " +
                        "Address = @Address, " +
                        "City = @City, " +
                        "Region = @Region, " +
                        "PostalCode = @PostalCode, " +
                        "Country = @Country, " +
                        "Phone = @Phone, " +
                        "Fax = @Fax " +
                    "WHERE " +
                        "CustomerId = @CustomerId";
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
                cmd.Parameters.AddWithValue("@ContactName", customer.ContactName);
                cmd.Parameters.AddWithValue("@ContactTitle", customer.ContactTitle);
                cmd.Parameters.AddWithValue("@Address", customer.Address);
                cmd.Parameters.AddWithValue("@City", customer.City);
                cmd.Parameters.AddWithValue("@Region", customer.Region);
                cmd.Parameters.AddWithValue("@PostalCode", customer.PostalCode);
                cmd.Parameters.AddWithValue("@Country", customer.Country);
                cmd.Parameters.AddWithValue("@Phone", customer.Phone);
                cmd.Parameters.AddWithValue("@Fax", customer.Fax);
                cmd.Parameters.AddWithValue("@CustomerId", customer.CustomerId);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in CustomerDataAccess.Update", ex);
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }

        public static void Delete(string customerid)
        {
            SqlConnection cn = null;
            try
            {
                string sql = "DELETE FROM Customers WHERE CustomerId = @CustomerId";
                cn = new SqlConnection(ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.AddWithValue("@CustomerId", customerid);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception in CustomerDataAccess.Delete", ex);
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }



        private static Customer DataReader2Customer(IDataRecord dr)
        {
            string customerid = dr["CustomerId"].ToString();
            string companyname = dr["CompanyName"].ToString();
            string contactname = dr["ContactName"].ToString();
            string contacttitle = dr["ContactTitle"].ToString();
            string address = dr["Address"].ToString();
            string city = dr["City"].ToString();
            string region = dr["Region"].ToString();
            string postalcode = dr["PostalCode"].ToString();
            string country = dr["Country"].ToString();
            string phone = dr["Phone"].ToString();
            string fax = dr["Fax"].ToString();

            return new Customer(customerid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax);
        }

        private static List<Customer> DataReader2CustomerList(IDataReader dr)
        {
            List<Customer> l = new List<Customer>();
            while (dr.Read())
                l.Add(DataReader2Customer(dr));
            return l;
        }


    }
}